package br.com.bancodecurriculos.controleacesso.dao.impl;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.bancodecurriculos.controleacesso.dao.IPerfilDAOHelper;
import br.com.bancodecurriculos.controleacesso.dominio.Perfil;
import br.com.bancodecurriculos.util.dao.JDBCConnectionFactory;
import br.com.bancodecurriculos.util.exception.RegistroExistenteException;
import br.com.bancodecurriculos.util.exception.RegistroInexistenteException;

public class PerfilDAOHelper implements IPerfilDAOHelper {

	/*
	 * private static final String SQL_INSERT = "perfil.insert"; private static
	 * final String SQL_UPDATE = "perfil.update"; private static final String
	 * SQL_SELECT = "perfil.select"; private static final String SQL_DELETE =
	 * "perfil.delete"; private static final String SQL_SELECT_BY_NAME =
	 * "perfil.getByName"; private static final String SQL_SELECT_ALL =
	 * "perfil.getAll";
	 * 
	 * private static final String PERFIL_EXISTENTE_EXCEPTION =
	 * "perfil.exception.perfil_existente"; private static final String
	 * PERFIL_INEXISTENTE_EXCEPTION = "perfil.exception.perfil_inexistente";
	 */

	private static final String SQL_INSERT = "INSERT INTO perfil (nome) VALUES (?)";
	private static final String SQL_UPDATE = "UPDATE perfil SET nome = ? WHERE id = ?";
	private static final String SQL_SELECT = "SELECT * FROM perfil WHERE id = ?";
	private static final String SQL_DELETE = "DELETE FROM perfil WHERE id = ?";
	private static final String SQL_SELECT_BY_NAME = "SELECT * FROM perfil WHERE nome = ?";
	private static final String SQL_SELECT_ALL = "SELECT * FROM perfil";

	private static final String PERFIL_EXISTENTE_EXCEPTION = "Perfil j� existe!";
	private static final String PERFIL_INEXISTENTE_EXCEPTION = "Perfil inexistente!";

	protected PerfilDAOHelper() {

	}

	@Override
	public void save(Perfil t) throws ClassNotFoundException, SQLException,
			RegistroExistenteException, IOException {
		if (this.getByName(t.getNome()) == null) {
			PreparedStatement preparedStatement = JDBCConnectionFactory
					.getInstance().getConnection().prepareStatement(SQL_INSERT);
			preparedStatement.setString(1, t.getNome());
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} else {
			throw new RegistroExistenteException(PERFIL_EXISTENTE_EXCEPTION);
		}
	}

	@Override
	public void update(Perfil t) throws ClassNotFoundException, SQLException,
			RegistroInexistenteException, IOException {
		if (this.getById(t.getId()) != null) {
			PreparedStatement preparedStatement = JDBCConnectionFactory
					.getInstance().getConnection().prepareStatement(SQL_UPDATE);
			preparedStatement.setString(1, t.getNome());
			preparedStatement.setLong(2, t.getId());
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} else {
			throw new RegistroInexistenteException(PERFIL_INEXISTENTE_EXCEPTION);
		}
	}

	@Override
	public Perfil getById(Long id) throws ClassNotFoundException, SQLException,
			IOException {
		Perfil perfil = null;
		PreparedStatement preparedStatement = JDBCConnectionFactory
				.getInstance().getConnection().prepareStatement(SQL_SELECT);
		preparedStatement.setLong(1, id);
		ResultSet resultSet = preparedStatement.executeQuery();
		if (resultSet.next()) {
			perfil = new Perfil(resultSet.getString("nome"));
			perfil.setId(resultSet.getLong("id"));
		}
		return perfil;
	}

	@Override
	public void delete(Long id) throws ClassNotFoundException, SQLException,
			RegistroInexistenteException, IOException {
		if (this.getById(id) != null) {
			PreparedStatement preparedStatement = JDBCConnectionFactory
					.getInstance().getConnection().prepareStatement(SQL_DELETE);
			preparedStatement.setLong(1, id);
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} else {
			throw new RegistroInexistenteException(PERFIL_INEXISTENTE_EXCEPTION);
		}
	}

	@Override
	public Perfil getByName(String name) throws ClassNotFoundException,
			SQLException, IOException {
		Perfil perfil = null;
		PreparedStatement preparedStatement = JDBCConnectionFactory
				.getInstance().getConnection()
				.prepareStatement(SQL_SELECT_BY_NAME);
		preparedStatement.setString(1, name);
		ResultSet resultSet = preparedStatement.executeQuery();
		if (resultSet.next()) {
			perfil = new Perfil(resultSet.getString("nome"));
			perfil.setId(resultSet.getLong("id"));
		}
		return perfil;
	}

	@Override
	public List<Perfil> getAll() throws ClassNotFoundException, SQLException,
			IOException {
		List<Perfil> perfils = new ArrayList<Perfil>();
		PreparedStatement preparedStatement = JDBCConnectionFactory
				.getInstance().getConnection().prepareStatement(SQL_SELECT_ALL);
		ResultSet resultSet = preparedStatement.executeQuery();
		while (resultSet.next()) {
			Perfil perfil = new Perfil(resultSet.getString("nome"));
			perfil.setId(resultSet.getLong("id"));
			perfils.add(perfil);
		}
		return perfils;
	}

}
